﻿Imports System.Collections.Generic
Imports System.Linq
Imports Crystal.Common.Model
Imports System.Data
Imports System.Linq.Expressions
Imports System.IO
Imports System.ComponentModel
Imports Crystal.CLL
Imports System.Data.SqlClient
Imports CrystalCommon.BLL

Public Class ImporteSourceManager
    Implements IDisposable

#Region "Constructors"

    Public Sub New()
    End Sub

#End Region

#Region "Methods"

    Public Sub Dispose() Implements IDisposable.Dispose
    End Sub

    Public Sub ClearStagingTable(ByVal pstrTableName As String, ByVal pintEntityID As Integer)
        CommonManager.ClearBulkStagingTable(pstrTableName, pintEntityID)
    End Sub

    Public Function GetSuggestions(ByVal pstrInvestorName As String, ByVal pstrFundName As String, ByVal pintProjectID As Int32, ByVal pintUniInvestorID As Integer, ByVal pintUniFundID As Integer, ByVal pstrInvAddress As String, ByVal pstrFundAddress As String) As IDataReader
        Dim Idrdata As IDataReader = Nothing
        Idrdata = DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_GetSuggestions", New Object() {pstrInvestorName, pstrFundName, pintProjectID, pintUniInvestorID, pintUniFundID, pstrInvAddress, pstrFundAddress})
        Return Idrdata
    End Function

    Public Function GetSuggestions(ByVal pstrInvestorName As String, ByVal pstrFundName As String, ByVal pintProjectID As Int32, ByVal pintUniInvestorID As Integer, _
                                   ByVal pintUniFundID As Integer, ByVal pstrInvAddress As String, ByVal pstrFundAddress As String, ByVal pblnIsExtinct As Boolean, _
                                   ByVal pintMappedInvestorID As Integer, ByRef pintMasterInvestorID As Integer, ByVal pintCustodianInvestorID As Integer, _
                                   ByVal pintInvestorSearchChar As Integer, ByVal pintFundSearchChar As Integer, ByVal pblnIsSuggestionSearch As Boolean, _
                                   ByVal pblnIsInvNameContains As Boolean, ByVal pblnIsInvAddressContains As Boolean, ByVal pblnIsFundNameContains As Boolean, _
                                   ByVal pblnIsFundAddressContains As Boolean) As DataSet
        Dim ldsdata As New DataSet

        Dim cmd As New SqlCommand
        Dim sqlParam(17) As SqlParameter
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_EDS_GetSuggestions"

        sqlParam(0) = New SqlParameter("@InvestorName", pstrInvestorName)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@FundName", pstrFundName)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ProjectID", pintProjectID)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@UniInvestorID", pintUniInvestorID)
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@UniFundID", pintUniFundID)
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@InvAddress", pstrInvAddress)
        cmd.Parameters.Add(sqlParam(5))
        sqlParam(6) = New SqlParameter("@FundAddress", pstrFundAddress)
        cmd.Parameters.Add(sqlParam(6))
        sqlParam(7) = New SqlParameter("@IsExtinct", pblnIsExtinct)
        cmd.Parameters.Add(sqlParam(7))
        sqlParam(8) = New SqlParameter("@MappedInvestorID", pintMappedInvestorID)
        cmd.Parameters.Add(sqlParam(8))
        sqlParam(9) = New SqlParameter("@MasterInvestorID", SqlDbType.Int)
        sqlParam(9).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(9))
        sqlParam(10) = New SqlParameter("@CustodianInvestorID", pintCustodianInvestorID)
        cmd.Parameters.Add(sqlParam(10))
        sqlParam(11) = New SqlParameter("@InvestorSearchChar", pintInvestorSearchChar)
        cmd.Parameters.Add(sqlParam(11))
        sqlParam(12) = New SqlParameter("@FundSearchChar", pintFundSearchChar)
        cmd.Parameters.Add(sqlParam(12))
        sqlParam(13) = New SqlParameter("@IsSuggestionSearch", pblnIsSuggestionSearch)
        cmd.Parameters.Add(sqlParam(13))
        sqlParam(14) = New SqlParameter("@IsInvNameContains", pblnIsInvNameContains)
        cmd.Parameters.Add(sqlParam(14))
        sqlParam(15) = New SqlParameter("@IsInvAddressContains", pblnIsInvAddressContains)
        cmd.Parameters.Add(sqlParam(15))
        sqlParam(16) = New SqlParameter("@IsFundNameContains", pblnIsFundNameContains)
        cmd.Parameters.Add(sqlParam(16))
        sqlParam(17) = New SqlParameter("@IsFundAddressContains", pblnIsFundAddressContains)
        cmd.Parameters.Add(sqlParam(17))

        ldsdata = CreateDataBase.ExecuteDataSet(cmd)
        If (cmd.Parameters(9).Value IsNot DBNull.Value) Then
            pintMasterInvestorID = CInt(cmd.Parameters(9).Value)
        End If

        Return ldsdata
    End Function

    Public Function InsertUpdateMappings(ByVal pintClientID As Int16, ByVal pintETemplateID As Int16, ByVal pintInvestorID As Integer, ByVal pintFundID As Integer, ByVal pstrMatchCol1 As String, ByVal pstrMatchCol2 As String, ByVal pstrMatchCol3 As String, ByVal pstrMatchCol4 As String, ByVal pstrMatchCol5 As String, ByVal pintUserID As Int16) As Integer
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_EDS_InsertUpdateMappings", New Object() {pintClientID, pintETemplateID, pintInvestorID, pintFundID, pstrMatchCol1, pstrMatchCol2, pstrMatchCol3, pstrMatchCol4, pstrMatchCol5, pintUserID})
    End Function

    Public Function GetMappings(ByVal pintEntityID As Integer, ByVal pintETemplateID As Int16, ByVal pintClientID As Int16, ByVal pintMappingType As Int16, ByVal pintUserID As Int16, ByVal pintSourceTypeID As Int16) As DataTable
        Dim ldtdata As New DataTable
        'ldtdata = DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_EDS_GetMappings", New Object() {pintEntityID, pintETemplateID, pintClientID, pintMappingType, pintUserID, pintSourceTypeID}).Tables(0)
        'Return ldtdata

        Dim cmd As New SqlCommand
        Dim sqlParam(5) As SqlParameter
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_EDS_GetMappings"

        sqlParam(0) = New SqlParameter("@EntityID", pintEntityID)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@ETemplateID", pintETemplateID)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ClientID", pintClientID)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@MappingType", pintMappingType)
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@UserID", pintUserID)
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@SourceTypeID", pintSourceTypeID)
        cmd.Parameters.Add(sqlParam(5))
        ldtdata = CreateDataBase.ExecuteDataSet(cmd).Tables(0)
        Return ldtdata
    End Function

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'This code retrieves the records which have duplicated match column values and different mapping provided. This code is not needed now, as such mappings should
    ''be ignored and it's positions should be used.
    Public Function GetInvalidMappingForDupMatchValues(ByVal pintEntityID As Integer, ByVal pintETemplateID As Int16, ByVal pintClientID As Int16, ByRef penmResult As DBResult) As DataTable
        'Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_ImportESource", New Object() {pintEntityID, pintListingID, pintProjectID, pintETemplateID, pintClientID, penmSourceTypeCode})
        Dim cmd As New SqlCommand
        Dim sqlParam(3) As SqlParameter
        Dim ldtResults As New DataTable
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_EDS_GetInvalidMappingForDupMatchValues"

        sqlParam(0) = New SqlParameter("@EntityID", pintEntityID)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@ETemplateID", pintETemplateID)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ClientID", pintClientID)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@Result", SqlDbType.Int)
        sqlParam(3).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(3))

        ldtResults = CreateDataBase.ExecuteDataSet(cmd).Tables(0)
        If (cmd.Parameters(3).Value IsNot DBNull.Value) Then
            penmResult = CInt(cmd.Parameters(3).Value)
        End If
        Return ldtResults
    End Function

    Public Function ImportESource(ByVal pintEntityID As Integer, ByVal pintListingID As Integer, ByVal pintProjectID As Integer, ByVal pintETemplateID As Int16, ByVal pintClientID As Int16, ByVal penmSourceTypeCode As SourceTypeCode, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String) As DataTable
        'Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_ImportESource", New Object() {pintEntityID, pintListingID, pintProjectID, pintETemplateID, pintClientID, penmSourceTypeCode})
        Dim cmd As New SqlCommand
        Dim sqlParam(8) As SqlParameter
        Dim ldtResults As New DataTable
        Dim ldsResults As DataSet = Nothing
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_EDS_ImportESource"

        sqlParam(0) = New SqlParameter("@EntityID", pintEntityID)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@ListingID", pintListingID)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ProjectID", pintProjectID)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@ETemplateID", pintETemplateID)
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@ClientID", pintClientID)
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@SourceTypeCode", penmSourceTypeCode.ToString)
        cmd.Parameters.Add(sqlParam(5))
        sqlParam(6) = New SqlParameter("@Result", SqlDbType.Int)
        sqlParam(6).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(6))
        sqlParam(7) = New SqlParameter("@ErrorMessage", SqlDbType.VarChar)
        sqlParam(7).Size = 8000
        sqlParam(7).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(7))
        sqlParam(8) = New SqlParameter("@UserID", CommonCLL.UserID)
        cmd.Parameters.Add(sqlParam(8))

        'CreateDataBase.ExecuteNonQuery(cmd)
        ldsResults = New DataSet
        ldsResults = CreateDataBase.ExecuteDataSet(cmd)
        If ldsResults.Tables.Count > 0 Then
            ldtResults = ldsResults.Tables(0)
        End If
        If (cmd.Parameters(6).Value IsNot DBNull.Value) Then
            penmResult = CInt(cmd.Parameters(6).Value)
        End If
        If (cmd.Parameters(7).Value IsNot DBNull.Value) Then
            pstrErrorMessage = cmd.Parameters(7).Value.ToString
        End If
        Return ldtResults
    End Function

    Public Sub InsertUpdateESourceSnapshot(ByVal pintEntityID As Integer, ByVal pstrESourcePath As String, ByVal pintRegularRadio As Int16, ByVal pblnRetail As Boolean, ByVal pblnCustodian As Boolean, ByVal pblnCreateNew As Boolean, ByVal pintAdvancedRadio As Int16, ByVal pintRowNumber As Integer, ByVal pintUserID As Int16, ByVal pblnIsCompleted As Boolean, ByVal mintTemplateID As Int16, ByVal pstrHiddenColumns As String, ByVal pintProjectID As Integer)
        DataFactory.CreateDataBase().ExecuteNonQuery("CR_sp_EDS_InsertUpdateESourceSnapshot", New Object() {pintEntityID, pstrESourcePath, pintRegularRadio, pblnRetail, pblnCustodian, pblnCreateNew, pintAdvancedRadio, pintRowNumber, pintUserID, pblnIsCompleted, mintTemplateID, pstrHiddenColumns, pintProjectID})
    End Sub

    Public Sub ManageEntityDoubtStatus(ByVal pintEntityID As Integer, ByVal pblnHasAnyDoubts As Boolean)
        DataFactory.CreateDataBase().ExecuteNonQuery("CR_sp_DE_ManageEntityDoubtStatus", New Object() {pintEntityID, pblnHasAnyDoubts})
    End Sub

    Public Function GetSnapshotFilePath(ByVal pintEntityID As Integer) As String
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_EDS_GetSnapshotFilePath", New Object() {pintEntityID})
    End Function

    Public Function GetScreenStatus(ByVal pintEntityID As Integer) As IDataReader
        Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_PRJ_SelectScreenStatus", New Object() {pintEntityID})
    End Function

    Public Sub GetUniClientMappingStatus(ByVal pintETemplateID As Int16, ByVal pintClientID As Int16, ByRef pblnIsClientMappingExists As Boolean, _
                                         ByRef pblnIsUniversalMappingExists As Boolean, ByRef pintCustodianInvestorID As Integer, ByRef pstrCustodianInvestor As String, _
                                         ByRef pintMInvestorTypeID As Int16, ByVal pintEntityID As Integer, ByVal pintProjectID As Integer)
        Dim cmd As New SqlCommand
        Dim sqlParam(9) As SqlParameter
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_EDS_GetUniClientMappingStatus"

        sqlParam(0) = New SqlParameter("@ETemplateID", pintETemplateID)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@ClientID", pintClientID)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@IsClientMappingExists", SqlDbType.Bit)
        sqlParam(2).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@IsUniversalMappingExists", SqlDbType.Bit)
        sqlParam(3).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@CustodianInvestorID", SqlDbType.Int)
        sqlParam(4).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@CustodianInvestor", SqlDbType.VarChar, 300)
        sqlParam(5).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(5))
        sqlParam(6) = New SqlParameter("@RetailMasterInvestorTypeID", SqlDbType.TinyInt)
        sqlParam(6).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(6))
        sqlParam(7) = New SqlParameter("@EntityID", pintEntityID)
        cmd.Parameters.Add(sqlParam(7))
        sqlParam(8) = New SqlParameter("@UserID", CommonCLL.UserID)
        cmd.Parameters.Add(sqlParam(8))
        sqlParam(9) = New SqlParameter("@ProjectID", pintProjectID)
        cmd.Parameters.Add(sqlParam(9))

        CreateDataBase.ExecuteNonQuery(cmd)
        If (cmd.Parameters(2).Value IsNot DBNull.Value) Then
            pblnIsClientMappingExists = CInt(cmd.Parameters(2).Value)
        End If

        If (cmd.Parameters(3).Value IsNot DBNull.Value) Then
            pblnIsUniversalMappingExists = CInt(cmd.Parameters(3).Value)
        End If

        If (cmd.Parameters(4).Value IsNot DBNull.Value) Then
            pintCustodianInvestorID = CInt(cmd.Parameters(4).Value)
        End If

        If (cmd.Parameters(5).Value IsNot DBNull.Value) Then
            pstrCustodianInvestor = CStr(cmd.Parameters(5).Value)
        End If

        If (cmd.Parameters(6).Value IsNot DBNull.Value) Then
            pintMInvestorTypeID = CStr(cmd.Parameters(6).Value)
        End If
    End Sub

    Public Function GetOtherAttributes(ByVal pstrData As String, ByVal pblnIsInvestor As Boolean, ByVal pintProjectID As Integer) As DataTable
        Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_EDS_GetOtherAttributes", New Object() {pstrData, pblnIsInvestor, pintProjectID}).Tables(0)
    End Function

    Public Function GetOtherAttributesForSnapshot(ByVal pintEntityID As Integer) As DataTable
        Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_EDS_GetOtherAttributesForSnapshot", New Object() {pintEntityID}).Tables(0)
    End Function

    Public Function GetSnapshots(ByVal pintListingID As Integer, ByVal pintEntityID As Integer, ByVal pblnIsCompleted As Boolean) As IDataReader
        Dim Idrdata As IDataReader = Nothing
        Idrdata = DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_GetSnapshots", New Object() {pintListingID, pintEntityID, pblnIsCompleted})
        Return Idrdata
    End Function

    Public Function GetTemplateMapping(ByVal TemplateID As Int16, ByVal pstrSourceTypeCode As String) As IDataReader
        Dim Idrdata As IDataReader = Nothing
        Idrdata = DataFactory.CreateDataBase().ExecuteReader("CR_sp_TMP_GetTemplateMappings", New Object() {TemplateID, pstrSourceTypeCode})
        Return Idrdata
    End Function

    Public Sub DeleteRecord(ByVal pstrXML As String)
        DataFactory.CreateDataBase().ExecuteNonQuery("CR_sp_ES_DeleteRecords", New Object() {pstrXML})
    End Sub
#End Region

End Class
